The NO-UNDO keyword on temp-tables and variables
You were instructed very early on in this book to define almost all variables using the NO-UNDO keyword. Also, in this chapter’s example, the temp-tables for Customer, Order, and OrderLine are NO-UNDO. Why is this?
When you define variables, Progress allocates what amounts to a record buffer for them, where each variable becomes a field in the buffer. There are in fact two such buffers, one for variables whose values can be undone when a transaction is rolled back and one for those that can’t. There is extra overhead associated with keeping track of the before-image for each variable that can be undone, and this behavior is rarely needed. If you are modifying a variable inside a transaction block (and it is important for your program logic that sets that variable’s value that it be undone if the transaction is undone), then you should define the variable without the NO-UNDO keyword.
Here’s a trivial example of when this might be useful. This little procedure lets you create and update Customer records in a REPEAT loop, and then shows you how many were created:
The REPEAT block defines the scope of a transaction. Each time Progress runs through the block is a separate transaction and, as each iteration completes successfully, the record created in that block is committed to the database. If you run the procedure, the REPEAT loop lets you enter Customers until you press ESCAPE, which in an OpenEdge session running on MS Windows is mapped to the END-ERROR key label. Each time it goes through the block, Progress creates a Customer, displays its new CustNum (assigned by the CREATE trigger for Customer), and prompts you for a Name. It is at this point that you can press ESCAPE when you’re done entering Customers. This undoes and leaves the current iteration of the REPEAT block. Because each iteration of the REPEAT block is a separate transaction, the final Customer you created is undone—it’s erased from the database.
But what about the iCount variable? Since this was defined as undoable (the default), the final change to its value is rolled back along with everything else, and its value is the actual number of records created and committed to the database, as shown in Figure 17–1.
As you enter the REPEAT block for the third time, Progress creates a third new Customer and increments iCount from 2 to 3. When you press ESCAPE, the final iteration of the REPEAT block is undone, and Customer 3115 is deleted from the database. The value of iCount is restored to 2, which was its value before that iteration of the block. (Note that the key value 3115 cannot be undone or reused, however, because it comes from a database sequence, and for performance reasons, these are not under transaction control).
If the variable were defined NO-UNDO, then after it is incremented from 2 to 3 its value would not be restored when the final transaction is undone, and the final DISPLAY statement would show its value as 3.
Relatively few variables need to be undone in this way, so to maximize performance you should define all other variables as NO-UNDO. (The Progress Editor macros do this for you when you type DVI, DVC, etc. into the Editor.) So why isn’t NO-UNDO the default? Quite simply, it didn’t at first occur to the developers of the language that most variables should be defined this way, so the first versions of Progress went out with undo variables as the default. Because of the commitment to maintaining the behavior of existing applications, the default has not changed with new releases.
The same consideration applies to temp-tables. Because temp-tables are really database tables that are not stored in a persistent database, they have almost all of the capabilities of true database tables, including the ability to be written to disk temporarily, the ability to have indexes, and the ability to participate in transactions. As with variables, your temp-tables are more efficient if you define them as NO-UNDO so that they are left out of transactions. Consider whenever you define a temp-table whether it really needs to be part of your transactions. If not, include the NO-UNDO keyword in its definition.